package com.feidian.helper.provider;


public class SQLConstants {
	
	public static final String SQL_CREATE_TABLE_DEGREE_TYPES = 
			"CREATE TABLE degree_types(_id INTEGER PRIMARY KEY AUTOINCREMENT,type INTEGER, name TEXT);";
	
	public static final String SQL_CREATE_TABLE_COLLEGES = 
			"CREATE TABLE colleges(_id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT, name TEXT);";
	
	public static final String SQL_CREATE_TABLE_PROFESSIONS = 
			"CREATE TABLE professions(_id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT, name TEXT,category TEXT, degree_type INTEGER);";
	
	public static final String SQL_CREATE_TABLE_COLLEGE_PROFESSIONS = 
			"CREATE TABLE college_professions(_id INTEGER PRIMARY KEY AUTOINCREMENT,college TEXT, profession TEXT);";
	
	public static final String SQL_CREATE_TABLE_CATEGORIES = 
			"CREATE TABLE categories(_id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT, name TEXT);";

	public static final String SQL_INSERT_TABLE_DEGREE_TYPES = 
			"INSERT OR IGNORE INTO degree_types(type,name) values(?,?);";
	
	public static final String SQL_INSERT_TABLE_COLLEGES = 
			"INSERT OR IGNORE INTO colleges(code,name) values(?,?);";
	
	public static final String SQL_INSERT_TABLE_PROFESSIONS = 
			"INSERT OR IGNORE INTO professions(code,name,category,degree_type) values(?,?,?,?);";
	
	public static final String SQL_INSERT_TABLE_COLLEGE_PROFESSIONS = 
			"INSERT OR IGNORE INTO college_professions(college,profession) values(?,?);";
	
	public static final String SQL_INSERT_TABLE_CATEGORIES = 
			"INSERT OR IGNORE INTO categories(code,name) values(?,?);";
	
	public static final String SQL_COLLEGE_PROFESSION_VIEW = 
			"create view college_profession_view as select p.code,p.name,p.category,p.degree_type,c.college from professions p join college_professions c where p.code = c.profession;";


}
